'''
数据库基本操作
'''
import pymysql


def query_table(connect):
    # 执行查询的sql语句，查询结果在游标对象中，游标类似于迭代器
    # 游标对象.fetchall()
    # cursor.fetchone()
    # cursor.fetchmany(n)
    sql_str = ''
    with connect.cursor(pymysql.cursors.DictCursor) as cursor:
        re = cursor.execute(sql_str)
        print(re, cursor)
        # cursor.fetchall获取查询的所有结果
        all_re = cursor.fetchall()

        print(cursor.fetchone())


def operate_table(connect):
    # 增删改
    # 增
    # sql_str = """
    #     insert into tb_student (stuname,stusex,stuage,stutel)
    #     values
    #     ('张三', 1, 30, '11122223333')
    # """
    print('----------插入学生信息----------')
    sql_str = """
        insert into tb_student (stuname,stusex,stuage,stutel)
        values %s;
    """
    str2 = ''
    while 1:
        name = input('name:')
        sex = int(input('sex:1/0'))
        age = int(input('age:'))
        tel = input('tel:')
        value = input('continue? y/n')
        str2 += "('%s',%d,%d,'%s')," % (name, sex, age, tel)
        if value == 'n':
            print(str2[:-1])
            sql_str = sql_str % str2[:-1]
            print(sql_str)
            break

    with connect.cursor() as cursor:
        cursor.execute(sql_str)


def create_table(connect):
    with connect.cursor() as cursor:
        # 创建表
        # try:
        #     sql_str = """
        #     create table tb_student
        #     (
        #         stuid int auto_increment,
        #         stuname varchar(10) not null,
        #         stuage int,
        #         stusex bit default 1,
        #         stutel varchar(11),
        #         primary key (stuid)
        #     );
        #     """
        #     cursor.execute(sql_str)
        # except:
        #     pass

        # 自定义表
        table_name = input('请输入需要创建表名：')
        cnames = []
        pre = table_name[:3]
        while True:
            cname = input('请输入需要创建字段名(q退出)：')
            if cname == 'q':
                break
            cnames.append(pre + cname + ' text,')
        str1 = """
            create table if not exists tb_%s
            (
                %sid int auto_increment,
                %s
                primary key (%sid)
            )
        """
        sql_str = str1 % (
            table_name,
            table_name[:3],
            ' '.join(cnames),
            table_name[: 3]
        )
        print(sql_str)
        cursor.execute(sql_str)


# 建立连接
def main():
    con = pymysql.connect(
        host='localhost',
        user='root',
        password='root',
        port=3306,
        charset='utf8',
        autocommit=True
    )
    # 切换数据库
    with con.cursor() as cursor:
        cursor.execute('use pytest;')
    # DDL
    create_table(con)
    # 增删改
    operate_table(con)
    # 查询
    query_table(con)

    con.close()


if __name__ == '__main__':
    main()
